package com.apes.fn.scm.crossCompany.transfer.repository;

import com.apes.fn.scm.crossCompany.transfer.model.TransferProduct;
import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;


public interface TransferProductRepository extends CustomRepository<TransferProduct, String> {
    @Query(value = "select * from transfer_product where export = :export and reception = :reception",nativeQuery = true)
    List<TransferProduct> findByExportAndReception(@Param("export")String export,@Param("reception")String reception);

    @Query(value = "select * from transfer_product where export = :export and reception is null",nativeQuery = true)
    List<TransferProduct> findByExport(@Param("export")String export);

    @Query(value = "select * from transfer_product where export is null and reception = :reception",nativeQuery = true)
    List<TransferProduct> findByReception(@Param("reception")String reception);

    @Query(value = "select * from transfer_product where export is null and reception is null",nativeQuery = true)
    List<TransferProduct> findByNull();

    @Query(value = "SELECT * " +
            "  FROM TRANSFER_PRODUCT " +
            " WHERE VALID = 1 AND ((EXPORT IS NULL AND RECEPTION IS NULL) " +
            "    OR (EXPORT = :export AND RECEPTION = :reception) " +
            "    OR (EXPORT = :export AND RECEPTION IS NULL) " +
            "    OR (EXPORT IS NULL AND RECEPTION = :reception))",nativeQuery = true)
    List<TransferProduct> findByALL(@Param("export")String export,@Param("reception")String reception);


    @Query(value = "" +
            " SELECT EXPORT, RECEPTION, SUPPLIER_ID FROM TRANSFER_PRODUCT " +
            " WHERE VALID = 1 AND ((EXPORT IS NULL AND RECEPTION IS NULL) " +
            "    OR (EXPORT = :export AND RECEPTION = :reception) " +
            "    OR (EXPORT = :export AND RECEPTION IS NULL) " +
            "    OR (EXPORT IS NULL AND RECEPTION = :reception))", nativeQuery = true)
    List<Map> findByALLTwo(@Param("export")String export,@Param("reception")String reception);

    @Query(value = "" +
            "SELECT EXPORT, RECEPTION, SUPPLIER_ID FROM TRANSFER_PRODUCT WHERE VALID = 1 AND SUPPLIER_ID IS NULL AND (EXPORT = '001072' OR EXPORT IS NULL) " +
            "UNION ALL " +
            "SELECT EXPORT, RECEPTION, SUPPLIER_ID FROM TRANSFER_PRODUCT WHERE VALID = 1 AND EXPORT IS NULL AND RECEPTION IS NOT NULL " +
            "UNION ALL " +
            "SELECT EXPORT, RECEPTION, SUPPLIER_ID FROM TRANSFER_PRODUCT WHERE VALID = 1 AND EXPORT IS NOT NULL AND RECEPTION IS NULL AND EXPORT = '001072' " +
            "UNION ALL " +
            "SELECT EXPORT, RECEPTION, SUPPLIER_ID FROM TRANSFER_PRODUCT WHERE VALID = 1 AND EXPORT IS NULL AND RECEPTION IS NULL " +
            "UNION ALL  " +
            "SELECT EXPORT, RECEPTION, SUPPLIER_ID FROM TRANSFER_PRODUCT WHERE VALID = 1 AND EXPORT IS NOT NULL AND RECEPTION IS NOT NULL AND EXPORT = '001072' ", nativeQuery = true)
    List<Map> findExportToDirectly();


    @Query(value = "" +
            "SELECT EXPORT, RECEPTION, SUPPLIER_ID FROM TRANSFER_PRODUCT WHERE VALID = 1 AND SUPPLIER_ID IS NULL AND (EXPORT != '001072' OR EXPORT IS NULL) " +
            "UNION ALL " +
            "SELECT EXPORT, RECEPTION, SUPPLIER_ID FROM TRANSFER_PRODUCT WHERE VALID = 1 AND EXPORT IS NULL AND RECEPTION IS NOT NULL " +
            "UNION ALL " +
            "SELECT EXPORT, RECEPTION, SUPPLIER_ID FROM TRANSFER_PRODUCT WHERE VALID = 1 AND EXPORT IS NOT NULL AND RECEPTION IS NULL AND EXPORT != '001072' " +
            "UNION ALL " +
            "SELECT EXPORT, RECEPTION, SUPPLIER_ID FROM TRANSFER_PRODUCT WHERE VALID = 1 AND EXPORT IS NULL AND RECEPTION IS NULL " +
            "UNION ALL  " +
            "SELECT EXPORT, RECEPTION, SUPPLIER_ID FROM TRANSFER_PRODUCT WHERE VALID = 1 AND EXPORT IS NOT NULL AND RECEPTION IS NOT NULL AND EXPORT != '001072' ", nativeQuery = true)
    List<Map> findExportNoDirectlyToAll();




}























